The Filestream setting in registry and in SQL Server configuration must match.

Overview

Finding ID	Version	Rule ID	IA Controls	Severity
V-79331	SQL6-D0-016900	SV-94037r1_rule		Medium

Description
Information systems are capable of providing a wide variety of functions and services. Some of the functions and services, provided by default, may not be necessary to support essential organizational operations (e.g., key missions, functions). It is detrimental for applications to provide, or install by default, functionality exceeding requirements or mission objectives. Applications must adhere to the principles of least functionality by providing only essential capabilities. There are two settings governing Filestream option, one in the registry (OS level) and one in SQL Server configuration. There is a separation of security concerns between the Windows and database administrators, and the same access level set for the Windows service needs to be set for the SQL Server instance.

Description

Information systems are capable of providing a wide variety of functions and services. Some of the functions and services, provided by default, may not be necessary to support essential organizational operations (e.g., key missions, functions). It is detrimental for applications to provide, or install by default, functionality exceeding requirements or mission objectives. Applications must adhere to the principles of least functionality by providing only essential capabilities. There are two settings governing Filestream option, one in the registry (OS level) and one in SQL Server configuration. There is a separation of security concerns between the Windows and database administrators, and the same access level set for the Windows service needs to be set for the SQL Server instance.

STIG	Date
MS SQL Server 2016 Instance Security Technical Implementation Guide	2018-03-09

Details

Check Text ( C-78925r1_chk )
Review the system documentation to see if FileStream is in use. If the use of Filestream is documented and authorized, execute the following query. DECLARE @EnableLevel INT; EXEC master.dbo.Xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer\Filestream', N'EnableLevel', @EnableLevel output; DECLARE @SqlSetting INT; SELECT @SqlSetting = CONVERT(INT, value) FROM sys.configurations WHERE NAME = 'filestream access level'; SELECT CASE WHEN @SqlSetting = @EnableLevel THEN 'No' ELSE 'Yes' END AS [RegistrySettingsAreDifferent] If the "RegistrySettingsAreDifferent" field returns a "Yes", this is a finding.

Check Text ( C-78925r1_chk )

Review the system documentation to see if FileStream is in use. If the use of Filestream is documented and authorized, execute the following query.

DECLARE @EnableLevel INT;
EXEC master.dbo.Xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer\Filestream',
N'EnableLevel',
@EnableLevel output;

DECLARE @SqlSetting INT;
SELECT @SqlSetting = CONVERT(INT, value)
FROM sys.configurations
WHERE NAME = 'filestream access level';

SELECT CASE WHEN @SqlSetting = @EnableLevel THEN 'No' ELSE 'Yes' END AS [RegistrySettingsAreDifferent]

If the "RegistrySettingsAreDifferent" field returns a "Yes", this is a finding.

Fix Text (F-86083r1_fix)
Review and adjust the FileStream permissions for the Windows service to match those of the SQL Server instance.